﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

using System.Data.SqlClient;

namespace MyNet.WebServices {

    using MyNet.WebServices.DataModel;
    using MyNet.WebServices.DataProviders.Facebook;

    public class MyNetDbService : IMyNetDbService {

        protected static String databaseServer = "yx2lf0ikbv.database.windows.net,1433";
        protected static String databaseName = "MyNetDB";
        protected static String username = "espinosa";
        protected static String password = "2Td.rggc";

        #region Sql Statements

        static String addContactSqlStatement    = "INSERT INTO mynet.Contact ( Firstname, Lastname ) VALUES ( N'{0}', N'{1}')";
        static String updateContactSqlStatement = "UPDATE mynet.Contact SET Firstname = '{0}', Lastname = '{1}' WHERE Id = {2} ";
        static String removeContactSqlStatement = "DELETE FROM mynet.Contact WHERE Id = {0}";

        static String addMultipleContactsSqlStatement = "INSERT INTO mynet.Contact ( Firstname, Lastname ) VALUES {0}";
        static String getAllContactsSqlStatement      = "SELECT * FROM mynet.Contact";
        static String removeAllContactsSqlStatement   = "DELETE FROM mynet.Contact";

        #endregion

        static String MyNetDbConnectionString;

        public MyNetDbService () {

            // Create DB Connection String
            var connectionString = new SqlConnectionStringBuilder {
                DataSource = databaseServer,
                InitialCatalog = databaseName,
                Encrypt = true,
                TrustServerCertificate = false,
                UserID = username,
                Password = password
            };

            MyNetDbConnectionString = connectionString.ToString ();

        } // Constructor


        #region Methods : ( Add | Update | Remove ) a Contact

        /// Add Contact
        public void addContact( Contact contact ) {

            // Firstname and Lastname may contain apostrophes '
            // They are replaced with double apostrophes '' before insertion in DB
            var firstname = contact.Firstname.Replace( "'", "''" );
            var lastname = contact.Lastname.Replace( "'", "''" );

            // Connect to MyNetDB
            using ( var dbConnection = new SqlConnection ( MyNetDbConnectionString ) ) {

                dbConnection.Open ();

                // Execute SQL statements
                using ( SqlCommand sqlCommand = dbConnection.CreateCommand () ) {

                    // Insert Contact in DB
                    sqlCommand.CommandText = String.Format( addContactSqlStatement, firstname, lastname );
                    sqlCommand.ExecuteNonQuery ();

                } // SqlCommand                

                dbConnection.Close ();

            } // dbConnection

        } // Method

        /// Update Contact
        public void updateContact( int contactID, Contact contact ) {

            // Firstname and Lastname may contain apostrophes '
            // They are replaced with double apostrophes '' before insertion in DB
            var firstname = contact.Firstname.Replace( "'", "''" );
            var lastname = contact.Lastname.Replace( "'", "''" );

            // Connect to MyNetDB
            using ( var dbConnection = new SqlConnection ( MyNetDbConnectionString ) ) {

                dbConnection.Open ();

                // Execute SQL statements
                using ( SqlCommand sqlCommand = dbConnection.CreateCommand () ) {

                    // Update Contact in DB
                    sqlCommand.CommandText = String.Format( updateContactSqlStatement, firstname, lastname, contactID );
                    sqlCommand.ExecuteNonQuery ();

                } // SqlCommand                

                dbConnection.Close ();

            } // dbConnection

        } // Method

        /// Remove Contact
        public void removeContact( int contactID ) {

            // Connect to MyNetDB
            using ( var dbConnection = new SqlConnection ( MyNetDbConnectionString ) ) {

                dbConnection.Open ();

                // Execute SQL statements
                using ( SqlCommand sqlCommand = dbConnection.CreateCommand () ) {

                    // Remove Contact from DB
                    sqlCommand.CommandText = String.Format( removeContactSqlStatement, contactID );
                    sqlCommand.ExecuteNonQuery ();

                } // SqlCommand                

                dbConnection.Close ();

            } // dbConnection

        } // Method

        #endregion

        #region Methods : ( Add | Get | Remove ) multiple Contacts

        /// Add List of Contacts 
        public void addContacts( List<Contact> contacts ) {

            // Connect to MyNetDB
            using ( var dbConnection = new SqlConnection ( MyNetDbConnectionString ) ) {

                dbConnection.Open ();

                // Execute SQL statements
                using ( SqlCommand sqlCommand = dbConnection.CreateCommand () ) {

                    // Insert List of Contacts
                    sqlCommand.CommandText = String.Format( addMultipleContactsSqlStatement, multipleContactsSqlStatementValue( contacts ) );
                    sqlCommand.ExecuteNonQuery ();

                } // SqlCommand                

                dbConnection.Close ();

            } // dbConnection


        } // Method

        /// Get All Contacts
        public List<Contact> getContacts() {

            List<Contact> contacts = null;

            // Connect to MyNetDB
            using ( var dbConnection = new SqlConnection ( MyNetDbConnectionString ) ) {

                dbConnection.Open ();

                // Execute SQL statements
                using ( SqlCommand sqlCommand = dbConnection.CreateCommand () ) {

                    // Query: Select ALL Contacts
                    sqlCommand.CommandText = getAllContactsSqlStatement;
                    SqlDataReader reader = sqlCommand.ExecuteReader ();

                    contacts = extractContactsFromSqlDataReader ( reader );

                } // SqlCommand                

                dbConnection.Close ();

            } // dbConnection

            return contacts;

        } // Method

        /// Remove All Contacts
        public void removeContacts() {

            // Connect to MyNetDB
            using ( var dbConnection = new SqlConnection ( MyNetDbConnectionString ) ) {

                dbConnection.Open ();

                // Execute SQL statements
                using ( SqlCommand sqlCommand = dbConnection.CreateCommand () ) {

                    // Remove Contact from DB
                    sqlCommand.CommandText = removeAllContactsSqlStatement;
                    sqlCommand.ExecuteNonQuery ();

                } // SqlCommand                

                dbConnection.Close ();

            } // dbConnection

        } // Method

        #endregion

        #region Helper Methods

        /// Produce List of Values used in INSERT statement
        protected String multipleContactsSqlStatementValue( List<Contact> contacts ) {

            // Construct List of Values to insert
            var values = new StringBuilder ();

            for ( int i = 0; i < contacts.Count; i++ ) {

                var value = "";

                // Firstname and Lastname may contain apostrophes '
                // They are replaced with double apostrophes '' before insertion in DB
                var firstname = contacts[i].Firstname.Replace ( "'", "''" );
                var lastname = contacts[i].Lastname.Replace ( "'", "''" );

                if ( i < contacts.Count - 1 )
                    value = String.Format ( "( N'{0}', N'{1}' )" + ", ", firstname, lastname );
                else
                    value = String.Format ( "( N'{0}', N'{1}' )", firstname, lastname );

                values.Append ( value );

            } // For


            return values.ToString();

        } // Method

        protected List<Contact> extractContactsFromSqlDataReader( SqlDataReader reader ) {

            // Create Contact object for every contact in MyNetDB
            var contacts = new List<Contact> ();

            while ( reader.Read () ) {

                var contact = new Contact {
                    Id = (int) reader["Id"],
                    Firstname = (String) reader["Firstname"],
                    Lastname = (String) reader["Lastname"]
                };

                contacts.Add ( contact );

            } // While

            return contacts;

        } // Method

        #endregion


        /// Import Friends of a Facebook User 
        public void addFacebookUserFriends( string facebookUser, string accessToken ) {

            // Import Friends from Facebook
            FacebookClient facebook = new FacebookClient ();
            List<FacebookUser> userFriends = facebook.getUserFriends ( facebookUser, accessToken );

            // Transform FacebookUser objects to Contact objects
            List<Contact> contacts = new List<Contact> ();

            foreach ( var friend in userFriends ) {

                var contact = new Contact {
                    Firstname = friend.first_name,
                    Lastname = friend.last_name
                };

                contacts.Add ( contact );

            }

            // Insert Contacts in DB
            addContacts ( contacts );

        } // Method




    } // Class

}
